<?php
/*
 * This Class Loads data based on the Zen Cart 1.3.8 SQL Schema into a test Database.
 * There are a minimum of Tables and Entries, to keep the testing environment contolled.
 * Each Insert is executed seperatly (slow, I know), so tables can be commented-out easily
 */

class ZenCartDatabase {
		
	private $db;
	
	function __construct() {
		if (TEST_DB_DATABASE != "test") throw new Exception("DummyZenCartDatabase: Be sure to select ONLY a test database for this!");
		$this->db = new Datasource(TEST_DB_SERVER, TEST_DB_DATABASE, TEST_DB_SERVER_USERNAME, TEST_DB_SERVER_PASSWORD);
		$this->db->execute("CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
	}
	
	private function createTables() {
		
		$this->db->execute("CREATE TABLE products (
							products_id int(11) NOT NULL auto_increment,
							products_type int(11) NOT NULL default '1',
							products_quantity float NOT NULL default '0',
							products_model varchar(32) default NULL,
							products_image varchar(64) default NULL,
							products_price decimal(15,4) NOT NULL default '0.0000',
							products_virtual tinyint(1) NOT NULL default '0',
							products_date_added datetime NOT NULL default '0001-01-01 00:00:00',
							products_last_modified datetime default NULL,
							products_date_available datetime default NULL,
							products_weight float NOT NULL default '0',
							products_status tinyint(1) NOT NULL default '0',
							products_tax_class_id int(11) NOT NULL default '0',
							manufacturers_id int(11) default NULL,
							products_ordered float NOT NULL default '0',
							products_quantity_order_min float NOT NULL default '1',
							products_quantity_order_units float NOT NULL default '1',
							products_priced_by_attribute tinyint(1) NOT NULL default '0',
							product_is_free tinyint(1) NOT NULL default '0',
							product_is_call tinyint(1) NOT NULL default '0',
							products_quantity_mixed tinyint(1) NOT NULL default '0',
							product_is_always_free_shipping tinyint(1) NOT NULL default '0',
							products_qty_box_status tinyint(1) NOT NULL default '1',
							products_quantity_order_max float NOT NULL default '0',
							products_sort_order int(11) NOT NULL default '0',
							products_discount_type tinyint(1) NOT NULL default '0',
							products_discount_type_from tinyint(1) NOT NULL default '0',
							products_price_sorter decimal(15,4) NOT NULL default '0.0000',
							master_categories_id int(11) NOT NULL default '0',
							products_mixed_discount_quantity tinyint(1) NOT NULL default '1',
							metatags_title_status tinyint(1) NOT NULL default '0',
							metatags_products_name_status tinyint(1) NOT NULL default '0',
							metatags_model_status tinyint(1) NOT NULL default '0',
							metatags_price_status tinyint(1) NOT NULL default '0',
							metatags_title_tagline_status tinyint(1) NOT NULL default '0',
							PRIMARY KEY  (products_id),
							KEY idx_products_date_added_zen (products_date_added),
							KEY idx_products_status_zen (products_status),
							KEY idx_products_date_available_zen (products_date_available),
							KEY idx_products_ordered_zen (products_ordered),
							KEY idx_products_model_zen (products_model),
							KEY idx_products_price_sorter_zen (products_price_sorter),
							KEY idx_master_categories_id_zen (master_categories_id),
							KEY idx_products_sort_order_zen (products_sort_order),
							KEY idx_manufacturers_id_zen (manufacturers_id)
							) TYPE=MyISAM;"
							);
							
		$this->db->execute("CREATE TABLE products_description (
							products_id int(11) NOT NULL auto_increment,
							language_id int(11) NOT NULL default '1',
							products_name varchar(64) NOT NULL default '',
							products_description text,
							products_url varchar(255) default NULL,
							products_viewed int(5) default '0',
							PRIMARY KEY  (products_id,language_id),
							KEY idx_products_name_zen (products_name)
							) TYPE=MyISAM;"
							);
		
		$this->db->execute("CREATE TABLE currencies (
							currencies_id int(11) NOT NULL auto_increment,
							title varchar(32) NOT NULL default '',
							code char(3) NOT NULL default '',
							symbol_left varchar(24) default NULL,
							symbol_right varchar(24) default NULL,
							decimal_point char(1) default NULL,
							thousands_point char(1) default NULL,
							decimal_places char(1) default NULL,
							value float(13,8) default NULL,
							last_updated datetime default NULL,
							PRIMARY KEY  (currencies_id)
							) TYPE=MyISAM;"
							);
		
		$this->db->execute("CREATE TABLE tax_rates (
							tax_rates_id int(11) NOT NULL auto_increment,
							tax_zone_id int(11) NOT NULL default '0',
							tax_class_id int(11) NOT NULL default '0',
							tax_priority int(5) default '1',
							tax_rate decimal(7,4) NOT NULL default '0.0000',
							tax_description varchar(255) NOT NULL default '',
							last_modified datetime default NULL,
							date_added datetime NOT NULL default '0001-01-01 00:00:00',
							PRIMARY KEY  (tax_rates_id),
							KEY idx_tax_zone_id_zen (tax_zone_id),
							KEY idx_tax_class_id_zen (tax_class_id)
							) TYPE=MyISAM;"
							);
		
		$this->db->execute("CREATE TABLE customers (
							customers_id int(11) NOT NULL auto_increment,
							customers_gender char(1) NOT NULL default '',
							customers_firstname varchar(32) NOT NULL default '',
							customers_lastname varchar(32) NOT NULL default '',
							customers_dob datetime NOT NULL default '0001-01-01 00:00:00',
							customers_email_address varchar(96) NOT NULL default '',
							customers_nick varchar(96) NOT NULL default '',
							customers_default_address_id int(11) NOT NULL default '0',
							customers_telephone varchar(32) NOT NULL default '',
							customers_fax varchar(32) default NULL,
							customers_password varchar(40) NOT NULL default '',
							customers_newsletter char(1) default NULL,
							customers_group_pricing int(11) NOT NULL default '0',
							customers_email_format varchar(4) NOT NULL default 'TEXT',
							customers_authorization int(1) NOT NULL default '0',
							customers_referral varchar(32) NOT NULL default '',
							customers_paypal_payerid VARCHAR(20) NOT NULL default '',
							customers_paypal_ec TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL,
							PRIMARY KEY  (customers_id),
							KEY idx_email_address_zen (customers_email_address),
							KEY idx_referral_zen (customers_referral(10)),
							KEY idx_grp_pricing_zen (customers_group_pricing),
							KEY idx_nick_zen (customers_nick),
							KEY idx_newsletter_zen (customers_newsletter)
							) TYPE=MyISAM;"
							);
			
		$this->db->execute("CREATE TABLE orders (
							orders_id int(11) NOT NULL auto_increment,
							customers_id int(11) NOT NULL default '0',
							customers_name varchar(64) NOT NULL default '',
							customers_company varchar(64) default NULL,
							customers_street_address varchar(64) NOT NULL default '',
							customers_suburb varchar(32) default NULL,
							customers_city varchar(32) NOT NULL default '',
							customers_postcode varchar(10) NOT NULL default '',
							customers_state varchar(32) default NULL,
							customers_country varchar(32) NOT NULL default '',
							customers_telephone varchar(32) NOT NULL default '',
							customers_email_address varchar(96) NOT NULL default '',
							customers_address_format_id int(5) NOT NULL default '0',
							delivery_name varchar(64) NOT NULL default '',
							delivery_company varchar(64) default NULL,
							delivery_street_address varchar(64) NOT NULL default '',
							delivery_suburb varchar(32) default NULL,
							delivery_city varchar(32) NOT NULL default '',
							delivery_postcode varchar(10) NOT NULL default '',
							delivery_state varchar(32) default NULL,
							delivery_country varchar(32) NOT NULL default '',
							delivery_address_format_id int(5) NOT NULL default '0',
							billing_name varchar(64) NOT NULL default '',
							billing_company varchar(64) default NULL,
							billing_street_address varchar(64) NOT NULL default '',
							billing_suburb varchar(32) default NULL,
							billing_city varchar(32) NOT NULL default '',
							billing_postcode varchar(10) NOT NULL default '',
							billing_state varchar(32) default NULL,
							billing_country varchar(32) NOT NULL default '',
							billing_address_format_id int(5) NOT NULL default '0',
							payment_method varchar(128) NOT NULL default '',
							payment_module_code varchar(32) NOT NULL default '',
							shipping_method varchar(128) NOT NULL default '',
							shipping_module_code varchar(32) NOT NULL default '',
							coupon_code varchar(32) NOT NULL default '',
							cc_type varchar(20) default NULL,
							cc_owner varchar(64) default NULL,
							cc_number varchar(32) default NULL,
							cc_expires varchar(4) default NULL,
							cc_cvv blob,
							last_modified datetime default NULL,
							date_purchased datetime default NULL,
							orders_status int(5) NOT NULL default '0',
							orders_date_finished datetime default NULL,
							currency char(3) default NULL,
							currency_value decimal(14,6) default NULL,
							order_total decimal(14,2) default NULL,
							order_tax decimal(14,2) default NULL,
							paypal_ipn_id int(11) NOT NULL default '0',
							ip_address varchar(96) NOT NULL default '',
							PRIMARY KEY  (orders_id),
							KEY idx_status_orders_cust_zen (orders_status,orders_id,customers_id),
							KEY idx_date_purchased_zen (date_purchased)
							) TYPE=MyISAM;"
							);
			
		$this->db->execute("CREATE TABLE orders_products (
							orders_products_id int(11) NOT NULL auto_increment,
							orders_id int(11) NOT NULL default '0',
							products_id int(11) NOT NULL default '0',
							products_model varchar(32) default NULL,
							products_name varchar(64) NOT NULL default '',
							products_price decimal(15,4) NOT NULL default '0.0000',
							final_price decimal(15,4) NOT NULL default '0.0000',
							products_tax decimal(7,4) NOT NULL default '0.0000',
							products_quantity float NOT NULL default '0',
							onetime_charges decimal(15,4) NOT NULL default '0.0000',
							products_priced_by_attribute tinyint(1) NOT NULL default '0',
							product_is_free tinyint(1) NOT NULL default '0',
							products_discount_type tinyint(1) NOT NULL default '0',
							products_discount_type_from tinyint(1) NOT NULL default '0',
							products_prid tinytext NOT NULL,
							PRIMARY KEY  (orders_products_id),
							KEY idx_orders_id_prod_id_zen (orders_id,products_id)
							) TYPE=MyISAM;"
							);
		
		$this->db->execute("CREATE TABLE orders_products_attributes (
							orders_products_attributes_id int(11) NOT NULL auto_increment,
							orders_id int(11) NOT NULL default '0',
							orders_products_id int(11) NOT NULL default '0',
							products_options varchar(32) NOT NULL default '',
							products_options_values text NOT NULL,
							options_values_price decimal(15,4) NOT NULL default '0.0000',
							price_prefix char(1) NOT NULL default '',
							product_attribute_is_free tinyint(1) NOT NULL default '0',
							products_attributes_weight float NOT NULL default '0',
							products_attributes_weight_prefix char(1) NOT NULL default '',
							attributes_discounted tinyint(1) NOT NULL default '1',
							attributes_price_base_included tinyint(1) NOT NULL default '1',
							attributes_price_onetime decimal(15,4) NOT NULL default '0.0000',
							attributes_price_factor decimal(15,4) NOT NULL default '0.0000',
							attributes_price_factor_offset decimal(15,4) NOT NULL default '0.0000',
							attributes_price_factor_onetime decimal(15,4) NOT NULL default '0.0000',
							attributes_price_factor_onetime_offset decimal(15,4) NOT NULL default '0.0000',
							attributes_qty_prices text,
							attributes_qty_prices_onetime text,
							attributes_price_words decimal(15,4) NOT NULL default '0.0000',
							attributes_price_words_free int(4) NOT NULL default '0',
							attributes_price_letters decimal(15,4) NOT NULL default '0.0000',
							attributes_price_letters_free int(4) NOT NULL default '0',
							products_options_id int(11) NOT NULL default '0',
							products_options_values_id int(11) NOT NULL default '0',
							products_prid tinytext NOT NULL,
							PRIMARY KEY  (orders_products_attributes_id),
							KEY idx_orders_id_prod_id_zen (orders_id,orders_products_id)
							) TYPE=MyISAM;"
							);
							
		$this->db->execute("CREATE TABLE orders_total (
							orders_total_id int(10) unsigned NOT NULL auto_increment,
							orders_id int(11) NOT NULL default '0',
							title varchar(255) NOT NULL default '',
							text varchar(255) NOT NULL default '',
							value decimal(15,4) NOT NULL default '0.0000',
							class varchar(32) NOT NULL default '',
							sort_order int(11) NOT NULL default '0',
							PRIMARY KEY  (orders_total_id),
							KEY idx_ot_orders_id_zen (orders_id),
							KEY idx_ot_class_zen (class)
							) TYPE=MyISAM;"
							);
		
		$this->db->execute("CREATE TABLE orders_status_history (
							orders_status_history_id int(11) NOT NULL auto_increment,
							orders_id int(11) NOT NULL default '0',
							orders_status_id int(5) NOT NULL default '0',
							date_added datetime NOT NULL default '0001-01-01 00:00:00',
							customer_notified int(1) default '0',
							comments text,
							PRIMARY KEY  (orders_status_history_id),
							KEY idx_orders_id_status_id_zen (orders_id,orders_status_id)
							) TYPE=MyISAM;"
							);
		
		$this->db->execute("CREATE TABLE zones_to_geo_zones (
							association_id int(11) NOT NULL auto_increment,
							zone_country_id int(11) NOT NULL default '0',
							zone_id int(11) default NULL,
							geo_zone_id int(11) default NULL,
							last_modified datetime default NULL,
							date_added datetime NOT NULL default '0001-01-01 00:00:00',
							PRIMARY KEY  (association_id),
							KEY idx_zones_zen (geo_zone_id,zone_country_id,zone_id)
							) TYPE=MyISAM;"
							);
		
		$this->db->execute("CREATE TABLE geo_zones (
							geo_zone_id int(11) NOT NULL auto_increment,
							geo_zone_name varchar(32) NOT NULL default '',
							geo_zone_description varchar(255) NOT NULL default '',
							last_modified datetime default NULL,
							date_added datetime NOT NULL default '0001-01-01 00:00:00',
							PRIMARY KEY  (geo_zone_id)
							) TYPE=MyISAM;"
							);
		
		$this->db->execute("CREATE TABLE zones (
							zone_id int(11) NOT NULL auto_increment,
							zone_country_id int(11) NOT NULL default '0',
							zone_code varchar(32) NOT NULL default '',
							zone_name varchar(32) NOT NULL default '',
							PRIMARY KEY  (zone_id),
							KEY idx_zone_country_id_zen (zone_country_id),
							KEY idx_zone_code_zen (zone_code)
							) TYPE=MyISAM;"
							);

		$this->db->execute("CREATE TABLE address_book (
							address_book_id int(11) NOT NULL auto_increment,
							customers_id int(11) NOT NULL default '0',
							entry_gender char(1) NOT NULL default '',
							entry_company varchar(64) default NULL,
							entry_firstname varchar(32) NOT NULL default '',
							entry_lastname varchar(32) NOT NULL default '',
							entry_street_address varchar(64) NOT NULL default '',
							entry_suburb varchar(32) default NULL,
							entry_postcode varchar(10) NOT NULL default '',
							entry_city varchar(32) NOT NULL default '',
							entry_state varchar(32) default NULL,
							entry_country_id int(11) NOT NULL default '0',
							entry_zone_id int(11) NOT NULL default '0',
							PRIMARY KEY  (address_book_id),
							KEY idx_address_book_customers_id_zen (customers_id)
							) TYPE=MyISAM;"
							);
							
		$this->db->execute("CREATE TABLE countries (
							countries_id int(11) NOT NULL auto_increment,
							countries_name varchar(64) NOT NULL default '',
							countries_iso_code_2 char(2) NOT NULL default '',
							countries_iso_code_3 char(3) NOT NULL default '',
							address_format_id int(11) NOT NULL default '0',
							PRIMARY KEY  (countries_id),
							KEY idx_countries_name_zen (countries_name),
							KEY idx_address_format_id_zen (address_format_id),
							KEY idx_iso_2_zen (countries_iso_code_2),
							KEY idx_iso_3_zen (countries_iso_code_3)
							) TYPE=MyISAM;"
							);
							
		$this->db->execute("CREATE TABLE configuration (
							configuration_id int(11) NOT NULL auto_increment,
							configuration_title text NOT NULL,
							configuration_key varchar(255) NOT NULL default '',
							configuration_value text NOT NULL,
							configuration_description text NOT NULL,
							configuration_group_id int(11) NOT NULL default '0',
							sort_order int(5) default NULL,
							last_modified datetime default NULL,
							date_added datetime NOT NULL default '0001-01-01 00:00:00',
							use_function text default NULL,
							set_function text default NULL,
							PRIMARY KEY  (configuration_id),
							UNIQUE KEY unq_config_key_zen (configuration_key),
							KEY idx_key_value_zen (configuration_key,configuration_value(10)),
							KEY idx_cfg_grp_id_zen (configuration_group_id)
							) TYPE=MyISAM;"
							);
	}

	private function fillTables() {
		 // the Sample customer password for Bill Smith is "password"		
		$this->db->execute("INSERT INTO customers VALUES ('1', 'm', 'Bill', 'Smith', '2001-01-01 00:00:00', 'root@localhost', '', '1', '12345', '', 'd95e8fa7f20a009372eb3477473fcd34:1c', '0', '0', 'TEXT', '0', '', '', '0') ");
		
		/* Needed Settings during the order process */
		$this->db->execute("INSERT INTO currencies VALUES (1,'US Dollar','USD','$','','.',',','2','1.0000', now()), (2,'Euro','EUR','&euro;','','.',',','2','0.7413', now());");
		$this->db->execute("INSERT INTO tax_rates VALUES (1, 1, 1, 1, 7.0, 'FL TAX 7.0%', now(), now());");
		$this->db->execute("INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (1,223,18,1,now());");
		$this->db->execute("INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,date_added) VALUES (1,'Florida','Florida local sales tax zone',now());");
		$this->db->execute("INSERT INTO zones VALUES (18,223,'FL','Florida');");		
		$this->db->execute("INSERT INTO address_book (address_book_id ,customers_id ,entry_gender ,entry_company ,entry_firstname ,entry_lastname ,entry_street_address ,entry_suburb ,entry_postcode ,entry_city ,entry_state ,entry_country_id ,entry_zone_id) VALUES ('1', '1', 'm', 'JustaDemo', 'Bill', 'Smith', '123 Any Avenue', '', '12345', 'LA', 'California', '223', '12')");							
		$this->db->execute("INSERT INTO countries VALUES (223,'United States','US','USA', '2');");
		$this->db->execute("INSERT INTO configuration (configuration_id ,configuration_title ,configuration_key ,configuration_value ,configuration_description ,configuration_group_id ,sort_order ,last_modified ,date_added ,use_function ,set_function) VALUES ('17', 'Basis of Product Tax', 'STORE_PRODUCT_TAX_BASIS', 'Shipping', 'On what basis is Product Tax calculated. Options are<br />Shipping - Based on customers Shipping Address<br />Billing Based on customers Billing address<br />Store - Based on Store address if Billing/Shipping Zone equals Store zone', '1', '21', NULL , '2007-12-01 12:44:34', NULL , 'zen_cfg_select_option(array(''Shipping'', ''Billing'', ''Store''), ')");
		$this->db->execute("INSERT INTO configuration (configuration_id ,configuration_title ,configuration_key ,configuration_value ,configuration_description ,configuration_group_id ,sort_order ,last_modified ,date_added ,use_function ,set_function) VALUES ('15', 'Display Prices with Tax', 'DISPLAY_PRICE_WITH_TAX', 'false', 'Display prices with tax included (true) or add the tax at the end (false)', '1', '21', NULL , '2007-12-01 12:44:34', NULL , 'zen_cfg_select_option(array(''true'', ''false''), ') ");							
		$this->db->execute("INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, use_function, set_function, date_added) VALUES ('Country', 'STORE_COUNTRY', '223', 'The country my store is located in <br /><br /><strong>Note: Please remember to update the store zone.</strong>', '1', '6', 'zen_get_country_name', 'zen_cfg_pull_down_country_list(', now());");		
		$this->db->execute("INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, use_function, set_function, date_added) VALUES ('Zone', 'STORE_ZONE', '18', 'The zone my store is located in', '1', '7', 'zen_cfg_get_zone_name', 'zen_cfg_pull_down_zone_list(', now());");
		$this->db->execute("INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Default Language', 'DEFAULT_LANGUAGE', 'en', 'Default Language', '6', '0', now());");
		$this->db->execute("INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Default Currency', 'DEFAULT_CURRENCY', 'USD', 'Default Currency', '6', '0', now());");
		$this->db->execute("INSERT INTO configuration (configuration_id ,configuration_title ,configuration_key ,configuration_value ,configuration_description ,configuration_group_id ,sort_order ,last_modified ,date_added ,use_function ,set_function)VALUES ('233', 'Subtract stock', 'STOCK_LIMITED', 'true', 'Subtract product in stock by product orders', '9', '2', NULL , '2007-12-01 12:44:34', NULL , 'zen_cfg_select_option(array(''true'', ''false''), ')");
		$this->db->execute("INSERT INTO configuration (configuration_id ,configuration_title ,configuration_key ,configuration_value ,configuration_description ,configuration_group_id ,sort_order ,last_modified ,date_added ,use_function ,set_function) VALUES ('237', 'OrdersProducts status in Catalog when out of stock should be set to', 'SHOW_PRODUCTS_SOLD_OUT', '0', 'Show OrdersProducts when out of stock<br /><br />0= set product status to OFF<br />1= leave product status ON', '9', '10', NULL , '2007-12-01 12:44:34', NULL , 'zen_cfg_select_option(array(''0'', ''1''), ') ");
		$this->db->execute("INSERT INTO configuration (configuration_id ,configuration_title ,configuration_key ,configuration_value ,configuration_description ,configuration_group_id ,sort_order ,last_modified ,date_added ,use_function ,set_function) VALUES ('372', 'ID for text and file products options values', 'PRODUCTS_OPTIONS_VALUES_TEXT_ID', '0', 'Numeric value of the products_options_values_id used by the text and file attributes.', '6', NULL , '2007-12-01 12:44:34', '2007-12-01 12:44:34', NULL , NULL)");
	}
	
	public function createSchema() {
		$this->dropSchema();
		$this->createTables();
		$this->fillTables();
	}
	
	public function dropSchema() {
		$this->db->execute("DROP TABLE IF EXISTS products;");
		$this->db->execute("DROP TABLE IF EXISTS products_description;");
		$this->db->execute("DROP TABLE IF EXISTS currencies;");
		$this->db->execute("DROP TABLE IF EXISTS tax_rates;");
		$this->db->execute("DROP TABLE IF EXISTS customers;");
		$this->db->execute("DROP TABLE IF EXISTS orders;");
		$this->db->execute("DROP TABLE IF EXISTS orders_products;");
		$this->db->execute("DROP TABLE IF EXISTS orders_products_attributes;");
		$this->db->execute("DROP TABLE IF EXISTS orders_status_history;");
		$this->db->execute("DROP TABLE IF EXISTS orders_total;");
		$this->db->execute("DROP TABLE IF EXISTS zones_to_geo_zones;");
		$this->db->execute("DROP TABLE IF EXISTS geo_zones;");
		$this->db->execute("DROP TABLE IF EXISTS zones;");
		$this->db->execute("DROP TABLE IF EXISTS address_book;");
		$this->db->execute("DROP TABLE IF EXISTS countries;");
		$this->db->execute("DROP TABLE IF EXISTS configuration;");
	}
}
?>
